package com.example.scientific_screen.service;

import com.alibaba.fastjson.JSONObject;
import com.example.scientific_screen.model.t_area;
import com.example.scientific_screen.model.t_investor;
import com.example.scientific_screen.vo.TechnologicalInnovationVo;
import com.tmsps.common.bean.BaseService;
import com.tmsps.common.bean.ParamBean;
import com.tmsps.ne4spring.page.Page;
import com.tmsps.ne4spring.utils.ChkUtil;
import com.tmsps.ne4spring.utils.StrUtil;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.stereotype.Service;
import org.springframework.web.util.WebUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author YinlinGeng
 * @date 2022/3/22 18:12
 */

@Service
public class ScientficService extends BaseService<t_investor> {

    public Page findAllInvestorsIndex(Page page, t_investor invastor) {
        StringBuffer sql = new StringBuffer();
        sql.append("from t_investor where 1=1 ");
        List<String> list = new ArrayList<String>();
        String name = invastor.getName();
        if(ChkUtil.isNotNull(name)){
            sql.append( "and name like ? ");
            list.add("%"+name+"%");
        }
        String industry = invastor.getIndustry();
        if(ChkUtil.isNotNull(industry)){
            sql.append("and industry like ? ");
            list.add("%"+industry+"%");
        }
        String address = invastor.getAddress();
        if(ChkUtil.isNotNull(address)){
            sql.append("and address like ? ");
            list.add("%"+address+"%");
        }
        sql.append("order by created desc");
        return baseService.pageinate(page, "select *", sql.toString(), list.toArray());
    }

    public Object findAllCompanyTotal(String prov, String city, String dist,String comType,String isSupply) {
        StringBuffer from = new StringBuffer();
        List<String> params = new ArrayList<String>();
        from.append(" from t_company c where c.status>=0 ");
        if(ChkUtil.isNotNull(prov)){
            from.append("and c.prov = ? ");
            params.add(prov);
        }
        if(ChkUtil.isNotNull(city)){
            from.append(" and c.city = ? ");
            params.add(city);
        }
        if(ChkUtil.isNotNull(dist)){
            from.append(" and c.dist = ? ");
            params.add(dist);
        }
        if(ChkUtil.isNotNull(comType)){
            if("0".equals(comType)){
                from.append(" and (c.comType=0 or c.comType is null) ");
            }else{
                from.append(" and c.comType = ? ");
                params.add(comType);
            }
        }
        if(ChkUtil.isNotNull(isSupply)){
            from.append(" and c.isSupply = ? ");
            params.add(isSupply);
        }
        from.append(" order by created asc");
        Map<String,Object> map = baseService.findObj("select count(1) cnt "+from.toString(),params.toArray());
        if(ChkUtil.isNotNull(map)){
            return map.get("cnt");
        }
        return 0;
    }



    public Object getTechContractTotal(JSONObject srh) {
        String proName=(String)srh.get("name");//合同名称
        String comName=(String)srh.get("comName");//甲乙方名称
        String dist=(String)srh.get("dist");//所属区
        String year=(String)srh.get("year");//所属年份

        List<Object> param = new ArrayList<Object>();
        StringBuffer from=new StringBuffer();
        from.append(" FROM t_tech_contract contract "
                + " WHERE contract.`status`>=0 AND contract.state=4 ");
        if(ChkUtil.isNotNull(proName)){
            from.append(" and (contract.proName like ? or contract.projectName like ? ) ");
            param.add("%"+proName+"%");
            param.add("%"+proName+"%");
        }
        if(ChkUtil.isNotNull(comName)){
            from.append(" and (contract.buyerName like ? or contract.sellerName like ? )");
            param.add("%"+comName+"%");
            param.add("%"+comName+"%");
        }
        if(ChkUtil.isNotNull(dist)){
            from.append(" and (contract.dist='"+dist+"' or contract.buyerDist='"+dist+"') ");
        }
        if(ChkUtil.isNotNull(year)){
            from.append(" and contract.signTime like '%"+year+"%' ");
        }
        from.append(" ORDER BY contract.signTime desc,contract.created desc    ");
        Map<String,Object> map = baseService.findObj("select count(1) cnt "+from.toString(),param.toArray());
        if(ChkUtil.isNotNull(map)){
            return map.get("cnt");
        }
        return 0;
    }


    public Object findAllDemandsTotal(String prov, String city, String dist) {
        String dprov=ChkUtil.isNotNull(prov)?"%"+prov+"%":null;
        String dcity=ChkUtil.isNotNull(city)?"%"+city+"%":null;
        String ddist=ChkUtil.isNotNull(dist)?"%"+dist+"%":null;
        StringBuffer from = new StringBuffer();
        List<String> params = new ArrayList<String>();
        from.append(" from t_demand d where status>0 ");
        if(ChkUtil.isNotNull(dprov)){
            from.append("and d.province like ? ");
            params.add(dprov);
        }
        if(ChkUtil.isNotNull(dcity)){
            from.append("and d.city like ? ");
            params.add(dcity);
        }
        if(ChkUtil.isNotNull(ddist)){
            from.append("and d.county like ? ");
            params.add(ddist);
        }
        from.append(" order by created asc");
        Map<String,Object> map = baseService.findObj("select count(1) cnt "+from.toString(),params.toArray());
        if(ChkUtil.isNotNull(map)){
            return map.get("cnt");
        }
        return 0;
    }



    public Map<String,Object> selectStatisContractMoney(){
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT SUM(contract.totalContractSum) totalSum,SUM(contract.payTechMoney) totalTechSum "
                + " FROM `t_tech_contract` contract "
                + " WHERE contract.`status`>=0 AND contract.state=4 ");
        return baseService.findObj(sb.toString());
    }


    public t_area findAreaByAreaId(String areaId) {
        return baseService.findObj("select * from t_area where status>=0 and areaId = ?", new Object[]{areaId}, t_area.class);
    }

    public String getNameById(String areaId) {
        if(ChkUtil.isNotNull(areaId)){
            t_area area = findAreaByAreaId(areaId);
            if(ChkUtil.isNotNull(area)){
                return area.getAreaName();
            }
        }
        return "";
    }

    public Object findAllIntellTotal(String provId, String cityId, String distId) {
        StringBuffer from = new StringBuffer();
        List<String> params = new ArrayList<String>();
        from.append(" from t_intellectual c where c.status>=0 ");
        if(ChkUtil.isNotNull(provId)){
            from.append("and (c.prov=? or c.address like ?)");
            params.add(provId);
            params.add("%"+getNameById(provId)+"%");
        }
        if(ChkUtil.isNotNull(cityId)){
            from.append(" and c.city = ? ");
            params.add(cityId);
        }
        if(ChkUtil.isNotNull(distId)){
            from.append(" and c.dist = ? ");
            params.add(distId);
        }
        from.append(" order by c.created asc");
        Map<String,Object> map = baseService.findObj("select count(1) cnt "+from.toString(),params.toArray());
        if(ChkUtil.isNotNull(map)){
            return map.get("cnt");
        }
        return 0;
    }




    public Object findAllAchieveTotal(String provId, String cityId, String distId) {
        StringBuffer from = new StringBuffer();
        List<String> params = new ArrayList<String>();
        from.append(" from t_achievements c where c.status>=1 ");
        if(ChkUtil.isNotNull(provId)){
            from.append("and (c.prov=? or c.county like ?)");
            params.add(provId);
            params.add("%"+getNameById(provId)+"%");
        }
        if(ChkUtil.isNotNull(cityId)){
            from.append(" and c.city = ? ");
            params.add(cityId);
        }
        if(ChkUtil.isNotNull(distId)){
            from.append(" and c.dist = ? ");
            params.add(distId);
        }
        from.append(" order by c.created asc");
        Map<String,Object> map = baseService.findObj("select count(1) cnt "+from.toString(),params.toArray());
        if(ChkUtil.isNotNull(map)){
            return map.get("cnt");
        }
        return 0;
    }


    public Map<String,Object> expertCount(String type) {
        return baseService.findObj("select count(eid) count from t_expert where type='"+type+"'");
    }


    public Page findOrgDemand(Page page, String name, String contact, String cid, String rootType) {
        StringBuffer from = new StringBuffer();
        List<String> params = new ArrayList<String>();
        from.append("from t_demand d left join t_base_service s on d.sid=s.sid  "
                + "LEFT JOIN t_serviceorg so ON d.orgid=so.sid  "
                + "LEFT OUTER JOIN t_scope sp on sp.iid = d.firstCateId "
                + "LEFT OUTER JOIN t_base_service bs on bs.sid = d.secIndustry "
                + "left outer join (select d.deid,GROUP_CONCAT(r.rcId) rcIds "
                + "from t_demand d LEFT JOIN t_recom r on r.deId=d.deid "
                + "where r.status=1 "
                + "GROUP BY d.deid) tr on tr.deid=d.deid "
                + "where d.status>=0  ");
        if(ChkUtil.isNotNull(name)){
            from.append("and d.name like ? ");
            params.add("%"+name+"%");
        }
        if(ChkUtil.isNotNull(contact)){
            from.append("and d.contact like ? ");
            params.add("%"+contact+"%");
        }
        if(ChkUtil.isNotNull(cid)){
            from.append("and d.cid=?");
            params.add(cid);
        }
        if(ChkUtil.isNotNull(rootType)){
            from.append(" and d.rootType=?");
            params.add(rootType);
        }
        from.append(" order by d.created desc");
        return baseService.pageinate(page, "select d.*,so.sname,s.sname,IF(d.rootType=0,'创业服务',(IF(d.rootType=1,'创新服务',''))) rootName,sp.`name` firstCateName,bs.sname secCateName,tr.rcIds rcIds",from.toString(),params.toArray());
    }


    public Page findAllCompanyPage(Page page, JSONObject srh) {
        String status = ChkUtil.isNotNull(srh.get("status"))?srh.getString("status"):null;
        String cname = ChkUtil.isNotNull(srh.get("cname"))?"%"+srh.getString("cname")+"%":null;
        String areaName = srh.getString("areaName");
        String bname = ChkUtil.isNotNull(srh.get("bname"))?"%"+srh.getString("bname")+"%":null;
        String keyWord = ChkUtil.isNotNull(srh.get("keyWord"))?"%"+srh.getString("keyWord")+"%":null;
        String address = ChkUtil.isNotNull(srh.get("address"))?"%"+srh.getString("address")+"%":null;
        String companyType = (String)srh.getString("companyType");
        String prov = (String)srh.getString("prov");
        String city = (String)srh.getString("city");
        String dist = (String)srh.getString("dist");
        String startDate = ChkUtil.isNotNull(srh.getString("startDate")) ? srh.getString("startDate") : "";
        String endDate = ChkUtil.isNotNull(srh.getString("endDate")) ? srh.getString("endDate") : "";
        // 是否是超级管理员
//        Boolean isAdmin = srh.getBoolean("isAdmin");


        StringBuffer sb=new StringBuffer();
        List<Object> param = new ArrayList<Object>();
        //	sb.append("from t_company c LEFT JOIN t_base b ON c.baseId=b.bid left join t_user u on c.cid=u.uid "
        sb.append("from (select c.*,b.baseName from t_company c LEFT JOIN t_base b ON c.baseId=b.bid  "
                + "where  c.cname is not null  and (c.comType = 0 or c.comType is null)   ");
        /*
         * 未完成数据： status = -50
         * 在科技成果需求征集平台展示的数据： status = 15
         * 县区管理员查看数据规则
         *    1、完成数据 status >= 0
         * 超级管理员查看数据
         *    1、完成数据 status >= 0
         *    2、未完成数据 status = -50
         */
//        if (isAdmin) {
            sb.append(" and (c.status >= 0 or  c.status = -50) ");
//        } else {
//            sb.append(" and c.status >= 0 ");
//        }

        if (ChkUtil.isNotNull(status)) {
            sb.append(" and c.status >= ? ");
            param.add(status);
        }

        if(ChkUtil.isNotNull(cname)){
            sb.append(" and c.cname like ? ");
            param.add(cname);
        }
        if(ChkUtil.isNotNull(bname)){
            sb.append(" and b.baseName like ? ");
            param.add(bname);
        }
        if(ChkUtil.isNotNull(companyType)){
            sb.append(" and c.companyType like ? ");
            param.add("%" + companyType + "%");
        }

        if(ChkUtil.isNotNull(prov)){
            sb.append(" and c.prov=? ");
            param.add(prov);
        }
        if(ChkUtil.isNotNull(city)){
            sb.append(" and c.city=? ");
            param.add(city);
        }
        if(ChkUtil.isNotNull(dist)){
            sb.append(" and  c.dist=? ");
            param.add(dist);
        }
        if(ChkUtil.isNotNull(areaName)){
            sb.append(" and  c.createArea=? ");
            param.add(areaName);
        }
        if(ChkUtil.isNotNull(address)){
            sb.append(" and c.address like ? ");
            param.add(address);
        }

        if(ChkUtil.isNotNull(startDate) ){
            sb.append(" and DATE_FORMAT(c.created,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d') ");
            param.add(startDate);
        }
        if (ChkUtil.isNotNull(endDate)) {
            sb.append(" and DATE_FORMAT(c.created,'%Y-%m-%d')<=DATE_FORMAT(?,'%Y-%m-%d')");
            param.add(endDate);
        }

        //查询添加时间>= showDate的数据
        if(ChkUtil.isNotNull(srh.get("showDate"))){
            sb.append(" and DATE_FORMAT(c.created,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d') ");
            param.add(srh.getString("showDate"));
        }

        if(srh.getInteger("approveStatus")!=null){
            if(srh.getInteger("approveStatus")==0){
                sb.append("and (c.approveStatus=? or c.approveStatus is null) ");
                param.add(srh.getString("approveStatus"));
            }else if("1".equals(srh.get("strip"))){
                sb.append("and (c.approveStatus=0 or c.approveStatus =2 or c.approveStatus is null) ");
            }else{
                sb.append("and c.approveStatus=? ");
                param.add(srh.getString("approveStatus"));
            }

        }else{
            if("1".equals(srh.get("strip"))){
                sb.append("and (c.approveStatus=0 or c.approveStatus =2 or c.approveStatus is null) ");
            }
        }

        if(StrUtil.notBlank(srh.getString("keyWord"))){
            sb.append(" and (c.cname like ? or c.contentText like ? or c.content like ? or c.cname in(SELECT a.unit from t_achievements a LEFT JOIN t_company c on c.cname =a.unit where a.name like ? or a.patent like ? or a.coop like ? or a.contrastText like ? "
                    + " or a.innovate like ? or a.srhKeywords like ? or a.acNote like ? "
                    + " or a.descriptionTxt like ? or a.summary like ? or a.techNoteTxt like ? "
                    + " or a.techAdvantageTxt like ? or a.techEffectTxt like ? or a.applicationNoteTxt like ? "
                    + " or a.holder like ? or a.unit like ? )) ");
            for(int i = 0; i < 18; i++){
                param.add("%"+srh.getString("keyWord")+"%");
            }
        }
        sb.append(" ) tmp order by case IFNULL(tmp.approveStatus,0) when 0 then 0 when -1 then 1 when 1 then 2  end asc,tmp.created desc ");
        return baseService.pageinate(page,"select tmp.* ",sb.toString(),param.toArray());
    }




    public TechnologicalInnovationVo technologicalInnovation(){
        t_investor investor = new t_investor();
        Page investorlist = findAllInvestorsIndex(new Page(),investor);
        TechnologicalInnovationVo technologicalInnovationVo = new TechnologicalInnovationVo();
        // 机构数
        technologicalInnovationVo.setOrganizations(investorlist.getTotalRow());
        // 企业数
        technologicalInnovationVo.setCompanies(Integer.valueOf(findAllCompanyTotal(null, null, null, "0", null).toString()));

        // 技术合同
        technologicalInnovationVo.setTechnicalContract(Integer.valueOf(getTechContractTotal(new JSONObject()).toString()));
        // 需求数
        technologicalInnovationVo.setDemand(Integer.valueOf(findAllDemandsTotal(null, null, null).toString()));


        Map<String, Object> objectMap = selectStatisContractMoney();
        // 技术合同
        technologicalInnovationVo.setTransactionAmount(objectMap.get("totalSum").toString());
        // 技术交易总额
        technologicalInnovationVo.setTechnicalAmount(objectMap.get("totalTechSum").toString());
        // 知识产权
        technologicalInnovationVo.setIntellectualProperty(Integer.valueOf(findAllIntellTotal(null, null, null).toString()));
        // 科技成果
        technologicalInnovationVo.setScientific(findAllAchieveTotal(null, null, null).toString());
        //专家
        Map<String, Object> objectMap1 = expertCount("0");
        technologicalInnovationVo.setAcademician(objectMap1.get("count").toString());
        // 研究者
        Map<String, Object> objectMap2 = expertCount("1");
        technologicalInnovationVo.setResearcher(objectMap2.get("count").toString());

        //科技需求
        technologicalInnovationVo.setTechnologicalDemands(Integer.valueOf(findOrgDemand(new Page(), null, null, null, "").getTotalRow()));
        //服务需求
        technologicalInnovationVo.setServiceNeeds(Integer.valueOf(findOrgDemand(new Page(), null, null, null, "").getTotalRow()));

        JSONObject gx = new JSONObject();
        gx.put("companyType",0);
        technologicalInnovationVo.setGxqy(findAllCompanyPage(new Page(),gx).getTotalRow());

        JSONObject qt = new JSONObject();
        qt.put("companyType",3);
        technologicalInnovationVo.setQt(findAllCompanyPage(new Page(),qt).getTotalRow());

        JSONObject zxqy = new JSONObject();
        zxqy.put("companyType",2);
        technologicalInnovationVo.setZxqy(findAllCompanyPage(new Page(),zxqy).getTotalRow());

        JSONObject gmys = new JSONObject();
        gmys.put("companyType",1);
        technologicalInnovationVo.setGmys(findAllCompanyPage(new Page(),gmys).getTotalRow());
        return technologicalInnovationVo;
    }


    @Override
    public ParamBean getFindPageParamBean(JSONObject jsonObject) {
        return null;
    }






}
